package com.trw.trp.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.trw.trp.bean.ListBean;
import com.utuan.log.Log;
import com.utuan.log.LogFactory;

public class ListDao extends AbstractDao {

	private static final String SQL_GET_LISTS = "select list_id,list_name,`desc` from trp.lists";
	private static final String SQL_GET_LIST = "select l.list_id,l.list_name,l.desc,li.item_name from trp.lists l,trp.list_items li where l.list_id=li.list_id and l.list_id=?";

	private static final String SQL_DELETE_ITEM = "delete from trp.list_items where list_id=?";
	private static final String SQL_ADD_ITEM = "insert into trp.list_items(list_id,item_name) values(?,?)";

	private static final String SQL_GET_AFFECTED_MODELS_BY_LIST_ID = "SELECT MODEL_ID FROM TRP.MODEL_FIELDS WHERE LIST_ID=?";

	ListDao() {
	}

	private static Log log = LogFactory.getLog(ListDao.class);

	public List<ListBean> getAllLists() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<ListBean> ret = new ArrayList<ListBean>();
		try {
			conn = getConnection();
			ps = conn.prepareStatement(SQL_GET_LISTS);
			rs = ps.executeQuery();
			while (rs.next()) {
				ListBean list = new ListBean();
				list.setId(rs.getLong(1));
				list.setName(rs.getString(2));
				list.setDesc(rs.getString(3));
				ret.add(list);
			}
		} catch (SQLException e) {
			log.warn(e);
			try {
				conn.rollback();
			} catch (SQLException e1) {
				log.warn(e1);
			}
		} finally {
			close(conn, rs, ps);
		}
		return ret;
	}

	public ListBean getList(long listId) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		ListBean list = null;
		try {
			conn = getConnection();
			ps = conn.prepareStatement(SQL_GET_LIST);
			ps.setLong(1, listId);
			rs = ps.executeQuery();
			List<ListBean.ItemBean> itemList = null;
			while (rs.next()) {
				long listId0 = rs.getLong(1);
				String listName0 = rs.getString(2);
				String itemName = rs.getString(4);
				String desc = rs.getString(3);
				if (list == null) {
					list = new ListBean();
					list.setId(listId0);
					list.setName(listName0);
					list.setDesc(desc);
					itemList = new ArrayList<ListBean.ItemBean>();
					list.setItemList(itemList);
				}
				ListBean.ItemBean item = new ListBean.ItemBean();
				item.setName(itemName);
				itemList.add(item);
			}
		} catch (SQLException e) {
			log.warn(e);
			try {
				conn.rollback();
			} catch (SQLException e1) {
				log.warn(e1);
			}
		} finally {
			close(conn, rs, ps);
		}
		return list;
	}

	public boolean saveList(ListBean list) {
		boolean ret = true;
		Connection conn = null;
		PreparedStatement ps = null;
		PreparedStatement ps2 = null;
		if (list == null) {
			return false;
		}
		try {
			conn = getConnection();
			// delete items
			ps = conn.prepareStatement(SQL_DELETE_ITEM);
			ps.setLong(1, list.getId());
			ps.execute();

			// add items
			ps2 = conn.prepareStatement(SQL_ADD_ITEM);
			List<ListBean.ItemBean> itemList = list.getItemList();
			if (itemList != null && !itemList.isEmpty()) {
				for (ListBean.ItemBean item : itemList) {
					ps2.setLong(1, list.getId());
					ps2.setString(2, item.getName());
					ps2.execute();
				}
			}
			conn.commit();
		} catch (SQLException e) {
			log.warn(e);
			try {
				conn.rollback();
			} catch (SQLException e1) {
				log.warn(e1);
			}
			ret = false;
		} finally {
			close(conn, null, ps, ps2);
		}
		return ret;
	}

	public List<Long> getAffectedModels(long listId) {
		List<Long> modelIdList = new ArrayList<Long>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			if (conn == null) {
				log.debug("can't get db connection.");
				return modelIdList;
			}
			ps = conn.prepareStatement(SQL_GET_AFFECTED_MODELS_BY_LIST_ID);
			ps.setLong(1, listId);
			rs = ps.executeQuery();
			while (rs.next()) {
				modelIdList.add(rs.getLong(1));
			}
		} catch (SQLException e) {
			log.warn(e);
		} finally {
			close(conn, rs, ps);
		}
		return modelIdList;
	}
}
